package com.qz.sql.api.controller.api;

import cn.hutool.json.JSONArray;
import cn.hutool.json.JSONObject;
import com.alibaba.druid.sql.SQLUtils;
import com.alibaba.druid.util.JdbcConstants;
import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.ExcelWriter;
import com.alibaba.excel.write.metadata.WriteSheet;
import com.alibaba.fastjson.JSON;
import com.alibaba.fastjson.serializer.SerializeConfig;
import com.alibaba.fastjson.serializer.SerializerFeature;
import com.alibaba.fastjson.serializer.SimpleDateFormatSerializer;
import com.baomidou.mybatisplus.core.conditions.update.UpdateWrapper;
import com.qz.sql.editor.bean.DatabaseRegistrationBean;
import com.qz.sql.editor.entity.DbDatasource;
import com.qz.sql.editor.entity.DbFavorite;
import com.qz.sql.editor.entity.DbHistory;
import com.qz.sql.editor.json.DocDbResponseJson;
import com.qz.sql.editor.json.ResponseJson;
import com.qz.sql.editor.mapper.base.ExecuteParam;
import com.qz.sql.editor.mapper.base.ExecuteResult;
import com.qz.sql.editor.mapper.base.ExecuteType;
import com.qz.sql.editor.mapper.base.SqlExecutor;
import com.qz.sql.editor.service.DbDatasourceService;
import com.qz.sql.editor.service.DbFavoriteService;
import com.qz.sql.editor.service.DbHistoryService;
import com.qz.sql.editor.utils.PoiUtils;
import com.qz.sql.editor.utils.SqlFormatterUtils;
import com.qz.sql.editor.utils.StringUtil;
import org.apache.commons.csv.CSVFormat;
import org.apache.commons.csv.CSVPrinter;
import org.apache.commons.lang.StringUtils;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.springframework.web.bind.annotation.CrossOrigin;
import org.springframework.web.bind.annotation.PostMapping;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;

import javax.annotation.Resource;
import javax.servlet.http.HttpServletResponse;
import java.io.OutputStream;
import java.io.OutputStreamWriter;
import java.net.URLEncoder;
import java.sql.Timestamp;
import java.text.SimpleDateFormat;
import java.util.*;

/**
 * @author 贾博
 * @Title: SQL编辑器对外提供api
 * @Package
 * @Description:
 * @date 2020/12/15
 */
@CrossOrigin(allowCredentials = "true", allowedHeaders = "*")
@RestController
@RequestMapping("/qz-sql/api")
public class SqlEditorApi {

    @Resource
    SqlExecutor sqlExecutor;
    @Resource
    DbHistoryService dbHistoryService;
    @Resource
    DbFavoriteService dbFavoriteService;

    @Resource
    DatabaseRegistrationBean databaseRegistrationBean;
    @Resource
    DbDatasourceService dbDatasourceService;
    private Long sourceId;
    private String executeId;
    private String sql;


    @PostMapping(value = "/history/list")
    public ResponseJson historyList(Long sourceId,Long createUserId) {
        UpdateWrapper<DbHistory> wrapper = new UpdateWrapper<>();
        wrapper.eq(sourceId != null, "datasource_id", sourceId);
        wrapper.eq( createUserId!= null, "create_user_id", createUserId);
        wrapper.eq("yn", 1);
        wrapper.orderByDesc("id");
        List<DbHistory> favoriteList = dbHistoryService.list(wrapper);
        return DocDbResponseJson.ok(favoriteList);
    }
    @PostMapping(value = "/favorite/list")
    public ResponseJson favoriteList(Long sourceId,Long CreateUserId) {
        UpdateWrapper<DbFavorite> wrapper = new UpdateWrapper<>();
        wrapper.eq(sourceId != null, "datasource_id", sourceId);
        wrapper.eq("create_user_id", CreateUserId);
        wrapper.eq("yn", 1);
        wrapper.orderByDesc("id");
        List<DbFavorite> favoriteList = dbFavoriteService.list(wrapper);
        return DocDbResponseJson.ok(favoriteList);

    }

    @PostMapping(value = "/favorite/add")
    public ResponseJson addFavorite(DbFavorite dbFavorite) {
        Integer yn = Optional.ofNullable(dbFavorite.getYn()).orElse(1);
        if (yn == 1) {
            if (StringUtils.isBlank(dbFavorite.getContent())) {
                return DocDbResponseJson.warn("收藏的SQL不能为空");
            }
            dbFavorite.setContent(dbFavorite.getContent().trim());
        }
        if (dbFavorite.getId() != null && dbFavorite.getId() > 0) {
            dbFavoriteService.updateById(dbFavorite);
        } else {
            dbFavorite.setCreateTime(new Date());
            dbFavorite.setCreateUserId(dbFavorite.getCreateUserId());
            dbFavorite.setYn(1);
            dbFavoriteService.save(dbFavorite);
        }
        return DocDbResponseJson.ok();
    }

    @PostMapping(value = "/execute")
    public ResponseJson execute(Long sourceId, String executeId, String sql) {
        if (StringUtils.isBlank(sql)) {
            return DocDbResponseJson.warn("执行的SQL不能为空");
        }
        // 保留历史记录
        dbHistoryService.saveHistory(sql.trim(), sourceId);
        List<String> resultList = new LinkedList<>();
        // 支持;分割的多个sql执行
        String[] sqlArr = sql.split(";");
        for (String sqlItem : sqlArr) {
            if (StringUtils.isBlank(sqlItem)) {
                continue;
            }
            sqlItem = sqlItem.trim();
            try {
                ExecuteType executeType = ExecuteType.ALL;
                ExecuteParam executeParam = new ExecuteParam();
                executeParam.setDatasourceId(sourceId);
                executeParam.setExecuteId(executeId);
                executeParam.setExecuteType(executeType);
                executeParam.setSql(sqlItem);
                executeParam.setMaxRows(1000);
                ExecuteResult executeResult = sqlExecutor.execute(executeParam);
                SerializeConfig mapping = new SerializeConfig();
                mapping.put(Date.class, new SimpleDateFormatSerializer("yyyy-MM-dd HH:mm:ss"));
                mapping.put(Timestamp.class, new SimpleDateFormatSerializer("yyyy-MM-dd HH:mm:ss"));
                String resultJsonStr = JSON.toJSONString(executeResult, mapping, SerializerFeature.WriteMapNullValue);
                resultList.add(resultJsonStr);
            } catch (Exception e) {
                ExecuteResult executeResult = ExecuteResult.error(StringUtil.getException(e), sqlItem);
                resultList.add(JSON.toJSONString(executeResult));
            }
        }
        return DocDbResponseJson.ok(resultList);
    }
    /**
     * 导出执行结果，传入sql语句，导出执行SQL 的所有文件
     * @param sourceId
     * @param exportType 1 excle 2 csv
     * @param sql
     * @return
     */
    @PostMapping(value = "/exportExecute")
    public ResponseJson exportExecute(Long sourceId, String executeId, String sql, Integer exportType,HttpServletResponse response) {
        this.sourceId = sourceId;
        this.executeId = executeId;
        this.sql = sql;
        if (StringUtils.isBlank(sql)) {
            return DocDbResponseJson.warn("执行的SQL不能为空");
        }
        dbHistoryService.saveHistory(sql.trim(), sourceId);
        List<String> resultList = new LinkedList<>();
        String[] sqlArr = sql.split(";");
        if(sqlArr.length>1){
            DocDbResponseJson.error("只支持单条SQL数据导出");
        }
        for (String sqlItem : sqlArr) {
            if (StringUtils.isBlank(sqlItem)) {
                continue;
            }
            sqlItem = sqlItem.trim();
            try {
                ExecuteType executeType = ExecuteType.ALL;
                ExecuteParam executeParam = new ExecuteParam();
                executeParam.setDatasourceId(sourceId);
                executeParam.setExecuteId(executeId);
                executeParam.setExecuteType(executeType);
                executeParam.setSql(sqlItem);
                executeParam.setMaxRows(1000);
                ExecuteResult executeResult = sqlExecutor.execute(executeParam);
                SerializeConfig mapping = new SerializeConfig();
                mapping.put(Date.class, new SimpleDateFormatSerializer("yyyy-MM-dd HH:mm:ss"));
                mapping.put(Timestamp.class, new SimpleDateFormatSerializer("yyyy-MM-dd HH:mm:ss"));
                String resultJsonStr = JSON.toJSONString(executeResult, mapping, SerializerFeature.WriteMapNullValue);
                JSONObject json = new JSONObject(resultJsonStr);
                JSONArray result = json.getJSONArray("result");
              if (exportType==1){
                  HSSFWorkbook sheets = PoiUtils.jsonToExcel(result);
                  // 配置文件下载
                  response.setHeader("content-type", "application/octet-stream");
                  response.setContentType("application/octet-stream");
                  // 下载文件能正常显示中文
                  response.setHeader("Content-Disposition", "attachment;filename=data.xls");
                  OutputStream os = response.getOutputStream();
                  sheets.write(os);
                  sheets.close();
                  os.close();
                  return null;
              }
                if (exportType==2){
                    // 配置文件下载
                    response.setHeader("content-type", "application/octet-stream");
                    response.setContentType("application/octet-stream");
                    // 下载文件能正常显示中文
                    response.setHeader("Content-Disposition", "attachment;filename=data.csv");
                    OutputStream os = response.getOutputStream();
                    OutputStreamWriter osw = new OutputStreamWriter(os, "GBK");
                    List<JSONObject> list =result.toList(JSONObject.class);
                    Set<String> set = list.get(0).keySet();
                    List<String> keys = new ArrayList<>(set);

                    String[] strings = keys.toArray(new String[keys.size()]);
                    CSVFormat csvFormat = CSVFormat.DEFAULT.withHeader(strings);
                    CSVPrinter csvPrinter = new CSVPrinter(osw, csvFormat);
                    Iterator<JSONObject> iterator = list.iterator();
                    while(iterator.hasNext()) {
                        JSONObject next = iterator.next();
                        for(String key : keys) {
                            csvPrinter.print(next.getStr(key));
                        }
                        csvPrinter.printRecord();
                    }
                    csvPrinter.flush();
                    csvPrinter.close();
                    return null;
                }
            } catch (Exception e) {
                ExecuteResult executeResult = ExecuteResult.error(e.getMessage(), sqlItem);
                resultList.add(JSON.toJSONString(executeResult));
            }
        }
        return null;
    }
    @PostMapping(value = "/update")
    public ResponseJson update(DbDatasource dbDatasource) {
        if (StringUtils.isBlank(dbDatasource.getName())) {
            return DocDbResponseJson.warn("名字必填");
        } else if (StringUtils.isBlank(dbDatasource.getDriverClassName())) {
            return DocDbResponseJson.warn("驱动类必选");
        } else if (StringUtils.isBlank(dbDatasource.getSourceUrl())) {
            return DocDbResponseJson.warn("地址必填");
        } else if (StringUtils.isBlank(dbDatasource.getSourceName())) {
            return DocDbResponseJson.warn("用户名必填");
        } else if (StringUtils.isBlank(dbDatasource.getSourcePassword())) {
            return DocDbResponseJson.warn("密码必填");
        }
        if (Objects.equals("***", dbDatasource.getSourcePassword())) {
            dbDatasource.setSourcePassword(null);
        }
        SimpleDateFormat ft = new SimpleDateFormat("yyyy-MM-dd hh:mm:ss");
        // 这三项不需要修改
        dbDatasource.setCreateTime(ft.format(new Date()));
        dbDatasource.setCreateUserId(null);
        dbDatasource.setCreateUserName(null);
        Long sourceId = Optional.ofNullable(dbDatasource.getId()).orElse(0L);
        if (sourceId > 0) {
            dbDatasourceService.updateById(dbDatasource);
            // 关闭旧数据源
            databaseRegistrationBean.closeDatasource(dbDatasource.getId());
        } else {
            dbDatasource.setCreateTime(ft.format(new Date()));
            dbDatasource.setCreateUserId(1L);
            dbDatasource.setCreateUserName("faker");
            dbDatasource.setYn(1);
            dbDatasourceService.save(dbDatasource);
        }
        return DocDbResponseJson.ok();
    }

    /**
     * 格式化SQL操作
     * @param sql
     * @return
     */
    @PostMapping(value = "/sqlFormat")
    public  ResponseJson sqlFormat(String sql){
        String format = new SqlFormatterUtils().format(sql);
        HashMap<String, String> map = new HashMap<>();
        map.put("formatsql",format);
        return DocDbResponseJson.ok(map);
    }


    public static void main(String[] args) {
        System.out.println(new SqlFormatterUtils().format(
                "select ta3.测试,bb.测试二,cc.测试三,dd.测试四五六七八 from 测试表二,(select ee,ff,gg from 测试表 where ee=ff) ta3 where aa=bb and cc=dd group by dd order by createtime desc limit 3"));

    }

}
